-- STANDARD SCRIPT
-- THE insurance1_prior_t TABLE IS LOADED 
--

\set ON_ERROR_STOP 1
CREATE OR REPLACE FUNCTION upd_insurance1_prior_f() RETURNS integer AS $$
DECLARE
  v_status integer;
  v_batch_run_date timestamp;
  v_cust_id integer;
BEGIN

SELECT INTO v_batch_run_date our_run_date from batch_insurance1_t limit 1;
SELECT INTO v_cust_id cust_id from batch_insurance1_t limit 1;

PERFORM ml_upd_batch_status_insurance1_new_f(v_cust_id,v_batch_run_date,1,9,'START LOADING DATA INTO insurance1_prior_t');


-- STEP 1 LOAD DATA INTO insurance1_prior_t    

RAISE NOTICE 'START LOADING DATA INTO insurance1_prior_t TABLE';


  INSERT INTO insurance1_prior_t
  SELECT 
      ba.cust_id,
      ba.individual_matchkey,
      ba.emdeon_sent_date,
      (case when ba.sum_tpl >= 1        then 1
            else                             0
            end),
      (case when ba.sum_commercial >= 1 then 1
            else                             0
            end),
      (case when ba.sum_medicaid >= 1   then 1
            else                             0
            end),
      (case when ba.sum_medicare_slow >= 1   then 1
            else                                  0
            end),
      (case when ba.sum_medicare_fast >= 1   then 1
            else                                  0
            end),
      'N' as loaded_insurance2
  FROM
     (SELECT 
          cust_id,
          individual_matchkey,
          max(emdeon_sent_date) as emdeon_sent_date,
          sum(case when emdeon_tpl_flag in ('1','2') and name_type <> 'G'        THEN 1
                   else                                                               0
                   end) as sum_tpl,
          sum(case when emdeon_commercial_flag in ('1','2') and name_type <> 'G' THEN 1
                   else                                                               0
                   end) as sum_commercial,
          sum(case when emdeon_medicaid_flag in ('1','2')   THEN 1
                   else                                          0
                   end) as sum_medicaid,
          sum(case when emdeon_medicare_flag in ('1','2') and emdeon_medicare_fast = '0'   THEN 1
                   else                                                                         0
                   end) as sum_medicare_slow,
          sum(case when emdeon_medicare_flag in ('1','2') and emdeon_medicare_fast = '1'   THEN 1
                   else                                                                         0
                   end) as sum_medicare_fast


      FROM
          batch_insurance1_t
      WHERE
          emdeon_sent_date IS NOT NULL and processing_type <> 'T' 
      GROUP BY
          cust_id,individual_matchkey
     ) as ba 
  ;

GET DIAGNOSTICS v_status = ROW_COUNT;
RAISE NOTICE 'FINISH LOADING DATA INTO insurance1_prior_t TABLE';


-- STEP2  UPDATE batch_status_insurance1_t WITH THE NUMBER UPDATED
-- UPDATE BATCH STATUS 

  UPDATE batch_status_insurance1_t
    SET
      insurance1_prior_inserted  = v_status
    WHERE
      (batch_status_insurance1_t.last_run_date = v_batch_run_date and batch_status_insurance1_t.cust_id = v_cust_id);


PERFORM ml_upd_batch_status_insurance1_new_f(v_cust_id,v_batch_run_date,1,9,'FINISH LOADING DATA INTO insurance1_prior_t');

  return v_status;
END;
$$ LANGUAGE plpgsql;
